//-----------------------------------------------------------------------
// <copyright file="ScriptExecutor.cs" company="Microsoft">
// Copyright (c) Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY 
// KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
// PARTICULAR PURPOSE.
// </copyright>
//-----------------------------------------------------------------------
namespace Microsoft.Sdc.Tasks.Configuration.SqlServer
{
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Globalization;
    using System.IO;
    using System.Reflection;
    using System.Text.RegularExpressions;

    /// <summary>
    /// Responsible for executing SQL scripts.
    /// </summary>
    internal sealed class ScriptExecutor
    {
        private static readonly Regex splitter = new Regex(@"^\s*GO\s+", RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.Multiline);

        /// <summary>
        /// A delegate type for hooking up to script file execution notifications.
        /// </summary>
        public delegate void ScriptExecutionHandler(object sender, ScriptFileEventArgs e);

        /// <summary>
        /// An event that clients can use to be notified when a SQL script 
        /// file has been executed.
        /// </summary>
        public static event ScriptExecutionHandler ScriptFileExecuted;

        /// <summary>
        /// Default constructor
        /// </summary>
        private ScriptExecutor()
        {
        }

        /// <summary>
        /// Executes a SQL script.
        /// </summary>
        /// <param name="executionInfo">
        /// Information about the script or scripts to be executed.
        /// </param>
        /// <remarks>
        /// <para>The input will be validated before the script is executed.
        /// If an incorrect combination of input has been supplied, an
        /// ApplicationException is raised. Valid inputs are defined below.</para>
        /// <para>ServerName - mandatory if ConnectionString is not specified.</para>
        /// <para>DatabaseName - mandatory if ConnectionString is not specified.</para>
        /// <para>ConnectionString - mandatory if ServerName and DatabaseName are not specified.</para>
        /// <para>CommandTimeout - optional and will default to 30 seconds if not specified.</para>
        /// <para>Path - mandatory if Sql is not specified.</para>
        /// <para>Sql - mandatory if Path is not specified.</para>
        /// <para>SubstitutionParameters - optional.</para>
        /// <para>Exclusions - only valid if Path is specified, wildcard characters are 
        /// not supported for exclusions.</para>
        /// </remarks>
        public static void Execute(ScriptExecutionInfo executionInfo)
        {
            // Will raise an exception if validation fails
            ValidateInput(executionInfo);
            string sqlCommandText;
            if (!IsEmpty(executionInfo.Sql))
            {
                // Excute SQL statement defined by text in executionInfo.Sql
                sqlCommandText = SubstituteParameters(executionInfo.Sql, executionInfo.SubstitutionParameters);
                ExecuteActionScriptSpecifyConnection(DetermineConnectionString(executionInfo), executionInfo.CommandTimeout, sqlCommandText);
            }
            else
            {
                // Excute SQL script files defined by executionInfo.Path
                FileInfo[] scriptFiles = GetFiles(executionInfo.Path);
                bool retry = true;
                int previousFailures = scriptFiles.Length;
                ApplicationException lastException = null;
                while (retry)
                {
                    ArrayList failures = new ArrayList(scriptFiles.Length);
                    foreach (FileInfo fileInfo in scriptFiles)
                    {
                        try
                        {
                            if (ShouldExecuteScript(fileInfo, executionInfo))
                            {
                                /* 1. Load the script file from disk
                                 * 2. Do parameter substitution before executing
                                 */
                                sqlCommandText = SubstituteParameters(LoadScript(fileInfo.FullName), executionInfo.SubstitutionParameters);
                                /* Append a whitespace character to the end of the command text.
                                 * Reason - some scripts have a "GO" statement at the end of 
                                 * the script and the regular expression to split up the batches 
                                 * does not detect it.
                                 */
                                sqlCommandText += '\n';

                                // Split the loaded script into batches delimited by the batch separator statement "GO"
                                string[] batches = splitter.Split(sqlCommandText);
                                SqlTransaction sqlTransaction = null;
                                using (SqlConnection sqlConnection = CreateConnection(DetermineConnectionString(executionInfo)))
                                {
                                    sqlConnection.Open();
                                    SqlCommand command = sqlConnection.CreateCommand();
                                    if (executionInfo.RollbackOnError)
                                    {
                                        sqlTransaction = sqlConnection.BeginTransaction();
                                    }

                                    try
                                    {
                                        // Execute each batch statement
                                        foreach (string batchText in batches)
                                        {
                                            sqlCommandText = batchText.Trim();
                                            if (sqlCommandText.Length > 0)
                                            {
                                                command.CommandText = sqlCommandText;
                                                command.CommandTimeout = executionInfo.CommandTimeout;
                                                command.Connection = sqlConnection;
                                                if (sqlTransaction != null)
                                                {
                                                    command.Transaction = sqlTransaction;
                                                }

                                                command.ExecuteNonQuery();
                                            }
                                        }

                                        if (sqlTransaction != null)
                                        {
                                            sqlTransaction.Commit();
                                        }
                                    }
                                    catch
                                    {
                                        if (sqlTransaction != null)
                                        {
                                            sqlTransaction.Rollback();
                                        }

                                        throw;
                                    }
                                    finally
                                    {
                                        sqlConnection.Close();
                                    }
                                }

                                OnScriptFileExecuted(new ScriptFileEventArgs(fileInfo));
                            }
                        }
                        catch (SqlException se)
                        {
                            string errorMsg = string.Format(
                                "Script file = {0}\r\nError = {1}",
                                fileInfo.Name,
                                se.Message);
                            lastException = new ApplicationException(errorMsg, se);
                            if (false == executionInfo.RetryFailures)
                            {
                                throw lastException;
                            }
                            else
                            {
                                failures.Add(fileInfo);
                                OnScriptFileExecuted(new ScriptFileEventArgs(fileInfo, se));
                            }
                        }
                    }

                    // Decide whether to retry or not.
                    if (false == executionInfo.RetryFailures)
                    {
                        retry = false;
                    }
                    else
                    {
                        if (failures.Count > 0)
                        {
                            if (failures.Count >= previousFailures)
                            {
                                throw lastException;
                            }

                            scriptFiles = null;
                            scriptFiles = new FileInfo[failures.Count];
                            failures.CopyTo(scriptFiles);
                            previousFailures = scriptFiles.Length;
                        }
                        else
                        {
                            retry = false;
                        }
                    }

                    failures = null;
                }
            }
        }

        /// <summary>
        /// Can be used to determine if a string is empty.
        /// </summary>
        /// <param name="valueToEvaluate">
        /// The string to evaluate.
        /// </param>
        /// <returns>
        /// Returns true if the string is null or empty. Otherwise, returns false.
        /// </returns>
        private static bool IsEmpty(string valueToEvaluate)
        {
            if (valueToEvaluate == null || valueToEvaluate.Length == 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// Validates the input script information. If validation fails, an
        /// ApplicationException is thrown.
        /// </summary>
        /// <param name="executionInfo">
        /// Input parameter to the Excute method that needs to be evaluated.
        /// </param>
        private static void ValidateInput(ScriptExecutionInfo executionInfo)
        {
            // Do we have enough information to connect to the database?
            if (IsEmpty(executionInfo.DatabaseName) &&
                IsEmpty(executionInfo.ServerName) &&
                IsEmpty(executionInfo.ConnectionString))
            {
                string errorMessage = "A database server and database name need to be specified to execute a SQL script.";
                throw new ApplicationException(FormatErrorString(errorMessage, executionInfo));
            }

            // Server name should not be empty if database name is specified
            if (!IsEmpty(executionInfo.DatabaseName) && IsEmpty(executionInfo.ServerName))
            {
                string errorMessage = "A database server needs to be specified in conjunction with a database name to execute a SQL script.";
                throw new ApplicationException(FormatErrorString(errorMessage, executionInfo));
            }

            // Too much information provided to connect to the database.
            if ((!IsEmpty(executionInfo.DatabaseName) ||
                 !IsEmpty(executionInfo.ServerName)) &&
                !IsEmpty(executionInfo.ConnectionString))
            {
                string errorMessage = "Too much information provided to connect to the database to execute a SQL script. Provide only a server name and a database name or only a connection string.";
                throw new ApplicationException(FormatErrorString(errorMessage, executionInfo));
            }

            // Need to specify a script to execute
            if (IsEmpty(executionInfo.Sql) &&
                IsEmpty(executionInfo.Path))
            {
                string errorMessage = "Please provide either a SQL statement or the path of a script file to execute.";
                throw new ApplicationException(FormatErrorString(errorMessage, executionInfo));
            }

            // Can only specify path or sql, not both
            if (!IsEmpty(executionInfo.Sql) &&
                !IsEmpty(executionInfo.Path))
            {
                string errorMessage = "Please provide only a SQL statement or only the path for a script file, but not both.";
                throw new ApplicationException(FormatErrorString(errorMessage, executionInfo));
            }
        }

        /// <summary>
        /// Executes a SQL statement and returns true if an item is found in the
        /// resulting result-set.
        /// </summary>
        /// <param name="databaseName">
        /// Name of the database that the script will be run against.
        /// </param>
        /// <param name="serverName">
        /// The SQL Server name.
        /// </param>
        /// <param name="sqlFormatScript">
        /// A string containing the SQL script to execute. The script may contain
        /// zero or more format items.
        /// </param>
        /// <param name="itemArgs">
        /// A string array containing zero or more items to format.
        /// </param>
        /// <returns>
        /// Returns true if an item exists in the resultant result-set, otherwise false.
        /// </returns>
        internal static bool ExecuteExistsScript(
            string databaseName,
            string serverName,
            string sqlFormatScript,
            params string[] itemArgs)
        {
            return ExecuteExistsScriptSpecifyConnection(
                DetermineConnectionString(databaseName, serverName),
                sqlFormatScript,
                itemArgs);
        }

        /// <summary>
        /// Executes a SQL statement and returns true if an item is found in the
        /// resulting result-set.
        /// </summary>
        /// <param name="connectionString">
        /// Database connection string.
        /// </param>
        /// <param name="sqlFormatScript">
        /// A string containing the SQL script to execute. The script may contain
        /// zero or more format items.
        /// </param>
        /// <param name="itemArgs">
        /// A string array containing zero or more items to format.
        /// </param>
        /// <returns>
        /// Returns true if an item exists in the resultant result-set, otherwise false.
        /// </returns>
        internal static bool ExecuteExistsScriptSpecifyConnection(
            string connectionString,
            string sqlFormatScript,
            params string[] itemArgs)
        {
            // Assume database item does not exist
            bool exists;

            // Escape single quote characters in input arguments to protect against sql injection attacks
            string[] modifiedArgs = new string[itemArgs.Length];
            for (int i = 0; i < modifiedArgs.Length; i++)
            {
                modifiedArgs[i] = itemArgs[i].Replace("'", "''");
            }

            // Prepare dynamic SQL statement to query the existence of the database item
            string commandText = string.Format(sqlFormatScript, modifiedArgs);

            // Connect to database
            using (SqlConnection sqlConnection = CreateConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, sqlConnection);
                sqlConnection.Open();
                SqlDataReader dataReader;
                dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);

                // Reader will return false if no matching entries found otherwise true
                exists = dataReader.Read();

                // Reader will automatically close connection
                dataReader.Close();
            }

            return exists;
        }

        /// <summary>
        /// Executes a SQL statement.
        /// </summary>
        /// <param name="databaseName">
        /// Name of the database that the script will be run against.
        /// </param>
        /// <param name="serverName">
        /// The SQL Server name.
        /// </param>
        /// <param name="sqlFormatScript">
        /// A string containing the SQL script to execute. The script may contain
        /// zero or more format items.
        /// </param>
        /// <param name="itemArgs">
        /// A string array containing zero or more items to format.
        /// </param>
        internal static void ExecuteActionScript(
            string databaseName,
            string serverName,
            string sqlFormatScript,
            params string[] itemArgs)
        {
            ExecuteActionScriptSpecifyConnection(
                DetermineConnectionString(databaseName, serverName),
                sqlFormatScript,
                itemArgs);
        }

        /// <summary>
        /// Executes a SQL statement with a command timeout period.
        /// </summary>
        /// <param name="databaseName">
        /// Name of the database that the script will be run against.
        /// </param>
        /// <param name="serverName">
        /// The SQL Server name.
        /// </param>
        /// <param name="commandTimeout">
        /// The wait time in seconds before terminating the attempt to execute 
        /// a command and generating an error.
        /// </param>
        /// <param name="sqlFormatScript">
        /// A string containing the SQL script to execute. The script may contain
        /// zero or more format items.
        /// </param>
        /// <param name="itemArgs">
        /// A string array containing zero or more items to format.
        /// </param>
        internal static void ExecuteActionScript(
            string databaseName,
            string serverName,
            int commandTimeout,
            string sqlFormatScript,
            params string[] itemArgs)
        {
            ExecuteActionScriptSpecifyConnection(
                DetermineConnectionString(databaseName, serverName),
                commandTimeout,
                sqlFormatScript,
                itemArgs);
        }

        /// <summary>
        /// Executes a SQL statement given a connection string.
        /// </summary>
        /// <param name="connectionString">
        /// Database connection string.
        /// </param>
        /// <param name="sqlFormatScript">
        /// A string containing the SQL script to execute. The script may contain
        /// zero or more format items.
        /// </param>
        /// <param name="itemArgs">
        /// A string array containing zero or more items to format.
        /// </param>
        internal static void ExecuteActionScriptSpecifyConnection(
            string connectionString,
            string sqlFormatScript,
            params string[] itemArgs)
        {
            ExecuteActionScriptSpecifyConnection(
                connectionString,
                30,
                sqlFormatScript,
                itemArgs);
        }

        /// <summary>
        /// Executes a SQL statement given a connection string and a command timeout period.
        /// </summary>
        /// <param name="connectionString">
        /// Database connection string.
        /// </param>
        /// <param name="commandTimeout">
        /// The wait time in seconds before terminating the attempt to execute 
        /// a command and generating an error.
        /// </param>
        /// <param name="sqlFormatScript">
        /// A string containing the SQL script to execute. The script may contain
        /// zero or more format items.
        /// </param>
        /// <param name="itemArgs">
        /// A string array containing zero or more items to format.
        /// </param>
        internal static void ExecuteActionScriptSpecifyConnection(
            string connectionString,
            int commandTimeout,
            string sqlFormatScript,
            params string[] itemArgs)
        {
            // Escape single quote characters in input arguments to protect against sql injection attacks
            string[] modifiedArgs = new string[itemArgs.Length];
            for (int i = 0; i < modifiedArgs.Length; i++)
            {
                modifiedArgs[i] = itemArgs[i].Replace("'", "''");
            }

            // Prepare dynamic SQL statement to execute
            string commandText = string.Format(sqlFormatScript, modifiedArgs);

            // Connect to database
            using (SqlConnection sqlConnection = CreateConnection(connectionString))
            {
                SqlCommand command = new SqlCommand(commandText, sqlConnection);
                command.CommandTimeout = commandTimeout;
                sqlConnection.Open();
                command.ExecuteNonQuery();
                sqlConnection.Close();
            }
        }

        /// <summary>
        /// Determines the connection string to use to connect to a SQL server
        /// database given the server name and the database name. Assumes
        /// Windows authentication.
        /// </summary>
        /// <param name="databaseName">
        /// Name of database to connect to.
        /// </param>
        /// <param name="serverName">
        /// Name of server to connect to.
        /// </param>
        /// <returns>
        /// Connection string that can be used to connect to a SQL Server database using a SqlConnection object.
        /// </returns>
        internal static string DetermineConnectionString(string databaseName, string serverName)
        {
            return string.Format(
                "Data Source={0};Integrated Security=SSPI;Initial Catalog={1}",
                serverName,
                databaseName);
        }

        /// <summary>
        /// Determines the connection string to use to connect to a SQL server
        /// database given the server name and the database name. Assumes
        /// Windows authentication.
        /// </summary>
        /// <param name="databaseName">
        /// Name of database to connect to.
        /// </param>
        /// <param name="serverName">
        /// Name of server to connect to.
        /// </param>
        /// <param name="timeout">
        /// Timeout value to apply to the connection attempt
        /// </param>
        /// <returns>
        /// Connection string that can be used to connect to a SQL Server database using a SqlConnection object.
        /// </returns>
        internal static string DetermineConnectionString(string databaseName, string serverName, int timeout)
        {
            return string.Format(
                "Data Source={0};Integrated Security=SSPI;Initial Catalog={1}",
                serverName,
                databaseName);
        }

        /// <summary>
        /// Constructs a database connection string based on the script execution
        /// information provided.
        /// </summary>
        /// <param name="executionInfo">
        /// The script information that requires a database connection to be executed.
        /// </param>
        /// <returns>
        /// A database connection string that can be passed to the SqlConnection constructor.
        /// </returns>
        private static string DetermineConnectionString(ScriptExecutionInfo executionInfo)
        {
            if (!IsEmpty(executionInfo.ConnectionString))
            {
                return executionInfo.ConnectionString;
            }
            else
            {
                return DetermineConnectionString(executionInfo.DatabaseName, executionInfo.ServerName);
            }
        }

        /// <summary>
        /// Finds paramters in the SQL script and replaces them with their
        /// corresponding values.
        /// </summary>
        /// <param name="sqlCommandText">
        /// The SQL command statement.
        /// </param>
        /// <param name="parameters">
        /// A hashtable of name value pairs where the key corresponds to the name
        /// of the parameter and where the value corresponds to the paramer's value.
        /// </param>
        /// <returns>
        /// SQL command text where the paramters found in the text have
        /// been replaced by their corresponding values.
        /// </returns>
        public static string SubstituteParameters(string sqlCommandText, Hashtable parameters)
        {
            string substitutedText = sqlCommandText;
            foreach (DictionaryEntry parameter in parameters)
            {
                substitutedText = substitutedText.Replace((string) parameter.Key, (string) parameter.Value);
            }

            return substitutedText;
        }

        /// <summary>
        /// Gets a list of files in the given path.
        /// </summary>
        /// <param name="path">
        /// The path to search which could contain the wildcard characters '*' and '?'.
        /// </param>
        /// <returns>
        /// Returns a file list from the path searched.
        /// </returns>
        private static FileInfo[] GetFiles(string path)
        {
            int dirPos = path.LastIndexOf(@"\");
            string dir;
            if (dirPos == -1)
            {
                AppDomain currApp = AppDomain.CurrentDomain;
                if (currApp != null)
                {
                    // If no directory is specified, use the directory of the calling application.
                    dir = currApp.SetupInformation.ApplicationBase.Substring(
                        0, currApp.SetupInformation.ApplicationBase.LastIndexOf(@"\"));
                }
                else
                {
                    // If no directory is specified, use the directory of the executing assembly.
                    Assembly currAssembly = Assembly.GetExecutingAssembly();
                    dir = currAssembly.Location.Substring(0, currAssembly.Location.LastIndexOf(@"\"));
                }
            }
            else
            {
                dir = path.Substring(0, dirPos + 1);
            }

            string filter = path.Substring(dirPos + 1);
            DirectoryInfo di = new DirectoryInfo(dir);
            FileInfo[] results = di.GetFiles(filter);

            // If the directory is valid and the file name is fully specified,
            // raise an error if the file is not found.
            if (results.Length == 0 && filter.IndexOf('*', 0) == -1 && filter.IndexOf('?', 0) == -1)
            {
                throw new FileNotFoundException("Script file not found", path);
            }

            return results;
        }

        /// <summary>
        /// Loads a SQL script from a text file.
        /// </summary>
        /// <param name="fileName">
        /// The name of the file that contains the SQL script.
        /// </param>
        /// <returns>
        /// The SQL script found in the text file.
        /// </returns>
        private static string LoadScript(string fileName)
        {
            string retValue;
            using (StreamReader textFileReader = new StreamReader(fileName, System.Text.Encoding.Default, true))
            {
                retValue = textFileReader.ReadToEnd();
                textFileReader.Close();
            }

            return retValue;
        }

        /// <summary>
        /// Decides whether to execute a script or not by checking the exclusions list.
        /// </summary>
        /// <param name="scriptFile">
        /// Script file that needs to be checked.
        /// </param>
        /// <param name="executionInfo">
        /// Hashtable of file exclusions
        /// </param>
        /// <returns>
        /// Returns true if the script should be executed, otherwise false.
        /// </returns>
        private static bool ShouldExecuteScript(FileSystemInfo scriptFile, ScriptExecutionInfo executionInfo)
        {
            if (executionInfo.Exclusions.Count > 0)
            {
                if (executionInfo.Exclusions.ContainsKey(scriptFile.FullName.ToLower(CultureInfo.InvariantCulture)))
                {
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// Fromats an error string with extra execution information.
        /// </summary>
        /// <param name="errorMessage">
        /// The error message to report.
        /// </param>
        /// <param name="executionInfo">
        /// Information about the scipt that caused the error.
        /// </param>
        /// <returns>
        /// Formatted error string.
        /// </returns>
        private static string FormatErrorString(string errorMessage, ScriptExecutionInfo executionInfo)
        {
            return string.Format(
                "Error = {0}\r\nScript {1} = {2}\r\n",
                errorMessage,
                (executionInfo.Path.Length > 0 ? "path" : "sql"),
                (executionInfo.Path.Length > 0 ? executionInfo.Path : executionInfo.Sql));
        }

        /// <summary>
        /// Called after a script file has finished executing to raise 
        /// ScriptFileExecuted events.
        /// </summary>
        /// <param name="scriptFileExecuted">
        /// Contains information about the script file that was executed.
        /// </param>
        private static void OnScriptFileExecuted(ScriptFileEventArgs scriptFileExecuted)
        {
            if (scriptFileExecuted != null && ScriptExecutor.ScriptFileExecuted != null)
            {
                ScriptExecutor.ScriptFileExecuted(null, scriptFileExecuted);
            }
        }

        /// <summary>
        /// Creates a SqlConnection with tracing enabled on the connection.
        /// </summary>
        /// <param name="connectionString">
        /// The database connection string.
        /// </param>
        /// <returns>
        /// A SqlConnection with tracing enabled on the connection.
        /// </returns>
        private static SqlConnection CreateConnection(string connectionString)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.InfoMessage += TraceMessageEventHandler;
            return connection;
        }

        /// <summary>
        /// The SqlConnecion.InfoMessage event handler.
        /// </summary>
        /// <param name="sender">
        /// The source of the event.
        /// </param>
        /// <param name="e">
        /// The event arguments.
        /// </param>
        private static void TraceMessageEventHandler(object sender, SqlInfoMessageEventArgs e)
        {
            if (ScriptExecutor.ScriptFileExecuted != null)
            {
                ScriptFileEventArgs args = new ScriptFileEventArgs(e.Errors);
                ScriptExecutor.ScriptFileExecuted(null, args);
            }
        }
    }
}